Create Dimensions Tables and Populate the Tables 11

Click  and download the SQL files and Store Procedures.

Run Script that is attached to create your Staging tables in the Staging Database. Once ran the tables will be made up of these columns.


*Using SSIS Toolbox, the following components will be drag in the Control Flow Dashboard:

1.      DFT-DimBusinessPropertyType

a.      Drag and drop a Data Flow Task, rename it as DFT-DimBusinessPropertyType and double click in it to get into edit mode

b.      Drag and drop OLE DB data source and double click it to edit. add connection manager and select the  SQL Command and use the query below to provide the required dataset for property types:

SELECT DISTINCT PropertyType
FROM [MarketDatamart_Staging].[dbo].[SubMarketVsMarket By PropertyTypes]
WHERE PropertyType <> 'NULL'

 

d.      Drag and drop a Derived Column transformation  double click the Derived Column transformation to edit and provide the derived columns as shown in the screenshot.



 

e.       Drag and drop a Data Conversion transformation  double click the Data Conversion transformation to edit and change the data type of  PropertyType to UnicodeString (25)

f.      Drag and drop OLE DB Destination and  double click it to  add connection manager, select the destination table  and map the columns.


2.      DFT-DimMarket

a.      Drag and drop a Data Flow Task, rename it as DFT - DimMarket and double click in it to get into edit mode

b.      Drag and drop OLE DB data source and double click it to add the  source connection manager and select the SQL Command and use the query below to provide the required dataset for property types:

 

SELECT distinct SubMarketCode, SubMarketName, PropertyType
FROM MarketDatamart_Staging.dbo.QuarterlySupplyDemandModelNational A
Left JOIN MarketDatamart_Staging.[dbo].SubMarketVsMarket By PropertyTypes B
ON A.Submarket = B.SubMarketDisplayName
WHERE SubMarketCode IS NOT NULL

 

c.      Drag and drop Lookup Transformation and double click it to configure. In the general tab  in the drop down select the ignore failure option and in connection select the destination connection manager and select  DimBusinessPropertyType table  and the column lookup for BusinessPropertyKey lookup.

d.      Drag and drop Lookup Transformation. In the general tab  in the drop down select the ignore failure option and in connection select the destination connection manager and select  DimMarket  table  and the column lookup for MArketKey lookup. 

e.       Drag and drop a Derived Column transformation and  double click it to edit and provide the derived columns as shown in the screenshot.


g. Drag and drop OLE DB Destination and double click it to add the destination connection manager, select the destination table and map the column in the mapping tab.

j.       Drag and drop a Lookup transformation and connect the flow from the lookup to lookup the MarketKey column in the DimMarket Table.

k.       Drag and drop OLE DB Command Transformation and double click it to add the connection manager and on the component Properties tab in the SQL Command add the below code  and then map them to the parameters in the column mapping .

UPDATE [dbo].[DimMarket]
SET [MarketName] =? , [CBSACode] =?,
[RecordFlag] ='History', [EffectiveDate] =getdate()-1,
[ModifiedDate] = getdate()
WHERE MarketKey  = ?

                                                 

3.      DFT – DimSubmarket

a.      Drag and drop a Data Flow Task, rename it as DFT – DimSubmarket and double click in it to get into edit mode

b.      Drag and drop OLE DB data source and double click it to edit. Connect to <Your SQL ServerName> dbo. SubMarketVsMarket By PropertyTypes in Data Warehouse area.

c.      Click on SQL Command and use the query below to provide the required dataset for property types:

SELECT distinct  MarketName, CBSA, PropertyType
FROM  [MarketDatamart_Staging].[dbo].[SubMarketVsMarket By PropertyTypes] A
LEFT JOIN [MarketDatamart_Staging].[dbo].[Vw_AllFips] B
ON A.MarketName = B.RCAName
LEFT JOIN [MarketDatamart_Staging].[dbo].[All_Fundamental]C
ON B.RCAName = C.MSA
LEFT JOIN [MarketDatamart_Staging].[dbo].[AllPivot] D
ON C.MSA = D.Metro
WHERE CBSA IS NOT NULL

d.      Drag and drop a Sort transformation onto the canvas and drop the precedence constraint from the data source above to it.

e.      Double click on one of the Sort Transformation to configure as in below screenshot to sort the source table as per columns you want to sort on in the derived dataset from source table.  Indicate the columns you want to output into the destination table as Pass Through.

f.       Drag and drop Lookup Transformation onto the canvas and drop precedence constraint from above onto it.  Double click it to configure as in screenshot shown to match the DimBusinessPropertyType table to ensure PropertyKey exists.

g.      Drag and drop Lookup Transformation onto the canvas and drop the Lookup Match Output precedence constraint from above onto it.  Double click to configure as in screenshot shown to match the DimSubmarket table to ensure Marketcode exists.

h.      Drag and drop a Derived Column transformation onto the canvas and drop the Lookup No Match Output precedence constraint from above onto it.

i.       Double click the Derived Column transformation to edit and provide the derived columns as shown in the screenshot.

j.       Drag and drop a Data Conversion transformation onto the canvas and drop the precedence constraint from above to it.

k.       Double click the Data Conversion transformation to edit and change the data type of affected column as shown in the screenshot.

l.       Drag and drop OLE DB Destination onto the canvas and drop the precedence constraint from above to it.

m.    Double click the OLE DB Destination to edit. Connect to <Your SQL ServerNamedbo.DimSubmarket in  the Data Warehouse area using connection manager and map the columns as shown in screenshot to insert new records into the target table.

n.      Drag and drop a Lookup transformation onto the canvas and drop the Lookup Match Output precedence constraint from the Lookup –SubmarketKey transformation above onto it.  This pipeline will be taken by records which will be modified in the target table.

o.     Double click the Lookup transformation to edit and match the DimSubmarket table to ensure SubmarketKey exists as shown in the screenshot.

p.      Drag and drop OLE DB Command Transformation onto the canvas for updating.  Drop precedence constraint onto it from above.  Double click it to configure as below screenshot for updating by providing:

o   The connection manager,

o   The T-SQL update script in SSIS format with parameters:

UPDATE dbo.DimSubMarket
SET SubMarketName =?,
SubMarketCode =?,
RecordFlag = 'History',
EffectiveDate = getdate() -1,
ModifiedDate = getdate()
WHERE SubMarketKey =?

o   Map the parameters to the relevant columns.

 

4.      DFT – DimStates

a.      Drag and drop a Data Flow Task, rename it as DFT – DimStates and double click in it to get into edit mode

b.      Drag and drop OLE DB data source and double click it to edit. Connect to <Your SQL ServerName> dbo.MarketToStateMapping in the Data Warehouse area.

c.      Click on SQL Command and use the query below to provide the required dataset for property types:

SELECT distinct B.[Geography], USStateCode
FROM [MarketDatamart_DW].[dbo].[MarketToStateMapping] A
LEFT JOIN [MarketDatamart_DW].[dbo].[FIPS_Moody] B
ON A.USStateCode = B.[GeoCode]
WHERE [GeoCode] IS NOT NULL

 

d.      Drag and drop a Derived Column transformation onto the canvas and drop the precedence constraint from the data source above to it.

e.      Double click the Derived Column transformation to edit and provide the derived columns as shown in the screenshot.

f.       Drag and drop a Data Conversion transformation onto the canvas and drop the precedence constraint from above to it.

g.       Double click the Data Conversion transformation to edit and change the data type of affected column as shown in the screenshot.

h.      Drag and drop OLE DB Destination onto the canvas and drop the precedence constraint from above to it.

i.       Double click the OLE DB Destination to edit. Connect to <Your SQL ServerName> dbo.DimState in the Data Warehouse area using connection manager and map the columns as shown in screenshot.

 

5.      DFT – DimRegion

a.      Drag and drop a Data Flow Task, rename it as DFT – DimRegion and double click in it to get into edit mode

b.      Drag and drop OLE DB data source and double click it to edit. Connect to <Your SQL ServerName> dbo.RegionSubRegionUSstates in the Data Warehouse area.

c.      Click on SQL Command and use the query below to provide the required dataset for property types:

SELECT distinct RegionName
FROM [MarketDatamart_Staging].[dbo].[RegionSubRegionUSstates]
WHERE RegionName <> 'NA'

 

d.      Drag and drop a Derived Column transformation onto the canvas and drop the precedence constraint from the data source above to it.

e.      Double click the Derived Column transformation to edit and provide the derived columns as shown in the screenshot.

f.       Drag and drop a Data Conversion transformation onto the canvas and drop the precedence constraint from above to it.

g.       Double click the Data Conversion transformation to edit and change the data type of affected column as shown in the screenshot.

h.      Drag and drop OLE DB Destination onto the canvas and drop the precedence constraint from above to it.

i.       Double click the OLE DB Destination to edit. Connect to <Your SQL ServerName> dbo.DimRegion in the Data Warehouse area using connection manager and map the columns as shown in screenshot.

 

6.      DFT – DimSubRegion

a.      Drag and drop a Data Flow Task, rename it as DFT – DimSubRegion and double click in it to get into edit mode

b.      Drag and drop OLE DB data source and double click it to edit. Connect to  <Your SQL ServerName> dbo.RegionSubRegionUSstates in the Data Warehouse area.

c.      Click on SQL Command and use the query below to provide the required dataset for property types:

 

SELECT distinct SubRegionName 
FROM [MarketDatamart_Staging].[dbo].[RegionSubRegionUSstates]
WHERE SubRegionName <> 'NA'

 

d.      Drag and drop a Derived Column transformation onto the canvas and drop the precedence constraint from the data source above to it.

e.      Double click the Derived Column transformation to edit and provide the derived columns as shown in the screenshot.

f.       Drag and drop a Data Conversion transformation onto the canvas and drop the precedence constraint from above to it.

g.       Double click the Data Conversion transformation to edit and change the data type of affected column as shown in the screenshot.

h.      Drag and drop OLE DB Destination onto the canvas and drop the precedence constraint from above to it.

i.       Double click the OLE DB Destination to edit. Connect to <Your SQL ServerName> dbo.DimSubRegion in the Data Warehouse area using connection manager and map the columns as shown in screenshot.

 

7.      Execute Store Procedure – DimCalenderDay

a.      Drag and drop a Execute SQL Task onto the canvas, rename it as “Execute Store Procedure – DimCalenderDay”  and double click in it to get into edit mode.

b.       Provide the connection as shown on the General Tab.

c.      Then click on Sql Command Tab and provide the T-SQL statement below to invoke stored procedure for creating the DimCalendar table in the Data Warehouse as shown in screenshot:

o   execdbo.uspDimCalendarday '01/01/1992', '12/31/2050'

 

8.      DFT- DimPeriodTypeTag

a.      Drag and drop a Data Flow Task, rename it as DFT- DimPeriodTypeTag and double click in it to get into edit mode

b.      Drag and drop OLE DB data source and double click it to edit. Connect to <Your SQL ServerName> dbo.VW_MarketStatisticSource in the Data Warehouse area.

c.      Click on SQL Command and use the query below to provide the required dataset for property types:

SELECT DISTINCT PeriodType from [dbo].[VW_MarketStatisticSource]

 

d.      Drag and drop a Derived Column transformation onto the canvas and drop the precedence constraint from the data source above to it.

e.      Double click the Derived Column transformation to edit and provide the derived columns as shown in the screenshot.

f.       Drag and drop a Data Conversion transformation onto the canvas and drop the precedence constraint from above to it.

g.       Double click the Data Conversion transformation to edit and change the data type of affected column as shown in the screenshot.

h.      Drag and drop OLE DB Destination onto the canvas and drop the precedence constraint from above to it.

i.       Double click the OLE DB Destination to edit. Connect to <Your SQL ServerName> dbo.DimPeriodTypeTag in the Data Warehouse area using connection manager and map the columns as shown in screenshot.

 

 

 

 


Populating the Dimesnion tables after executing the package